package dfbz.com.dao;

import dfbz.com.constant.Constance;
import dfbz.com.dao.base.BaseDao;
import dfbz.com.model.Article;
import dfbz.com.utils.JDBCUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ArticleDao extends BaseDao {

    private QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
    private BasicRowProcessor bp = new BasicRowProcessor(new GenerousBeanProcessor());

    public List findArticle(String current, String articleName) {
//        select t1.*,count(f.a_id) as count
//from
//(select a.*
//from article a
//where a.title like ？) t1
//left join favorite f
//on t1.id = f.a_id
//group by t1.id
//order by t1.id
//limit ？,？;
        //这里写查询的sql
        StringBuilder sb = new StringBuilder();
        sb.append(" select t1.*,count(f.a_id) as count from (select a.* from article a ");
        if (articleName != null) {
            sb.append(" where a.title like ?) t1 ");
            sb.append(" left join favorite f on t1.id = f.a_id group by t1.id order by t1.id limit ?,?;");
            try {
                List<Map<String, Object>> query = qr.query(sb.toString(), new MapListHandler(), articleName + "%", (Integer.parseInt(current) - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
                return query;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            sb.append(")t1 ");
            sb.append(" left join favorite f on t1.id = f.a_id group by t1.id order by t1.id limit ?,?");
            try {
                List<Map<String, Object>> query = qr.query(sb.toString(), new MapListHandler(), (Integer.parseInt(current) - 1) * Constance.PAGE_ROW_COUNT, Constance.PAGE_ROW_COUNT);
                return query;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public Long findTotalCount(String articleName) {
        StringBuilder sb = new StringBuilder();
        sb.append("select count(*) as count from article");
        if (articleName != null) {
            try {
                sb.append(" where title like ? ");
                Map<String, Object> query = qr.query(sb.toString(), new MapHandler(), articleName + "%");
                Object count = query.get("count");
                return (Long) count;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                Map<String, Object> query = qr.query(sb.toString(), new MapHandler());
                Object count = query.get("count");
                return (Long) count;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0L;
    }

    public Map<String, Object> findArticleById(String id) {

        try {
            String sql = "select t1.*,count(f.a_id) as count from \n" +
                    "(select a.*\n" +
                    "from article a\n" +
                    "where a.id = ?) t1\n" +
                    "left join favorite f\n" +
                    "on t1.id = f.a_id \n" +
                    "group by t1.id;";
            Map<String, Object> query = qr.query(sql, new MapHandler(), Integer.parseInt(id));
//            Article query = qr.query(sql, new BeanHandler<>(Article.class, bp), Integer.parseInt(id));
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List findCollectArticleList(String current, String articleName, String id) {

        List<Map<String, Object>> query = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select t1.* from\n" +
                "(select a.*, count(f.u_id) as count from article a left join favorite f on a.id = f.a_id group by a.id) t1  where t1.id in\n" +
                "(select f.a_id from favorite f where u_id =?) ");
        if (articleName != null) {
            sb.append(" and  t1.title like ?\n" +
                    "limit ?,?; ");
            try {
                query = qr.query(sb.toString(), new MapListHandler(), Integer.parseInt(id),
                        articleName + "%", (Integer.parseInt(current) - 1) * Constance.PAGE_ROW_COUNT,
                        Constance.PAGE_ROW_COUNT
                );
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            sb.append(" limit ?,?;");
            try {
                query = qr.query(sb.toString(), new MapListHandler(), Integer.parseInt(id), (Integer.parseInt(current) - 1) * Constance.PAGE_ROW_COUNT,
                        Constance.PAGE_ROW_COUNT
                );
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return query;
    }

    public Long findCollectTotalCount(String articleName, String userId) {

        Map<String, Object> query = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select count(*) as count from\n" +
                "(select * \n" +
                "from favorite \n" +
                "where u_id = ?) f\n" +
                "left join article a\n" +
                "on f.a_id=a.id ");
        if (articleName != null) {
            try {
                sb.append(" where a.title like ? ");
                query = qr.query(sb.toString(), new MapHandler(), Integer.parseInt(userId), articleName + "%");

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            try {
                query = qr.query(sb.toString(), new MapHandler(), Integer.parseInt(userId));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return (Long) query.get("count");


    }

    public Integer ifCollect(Integer bId, Integer uId) {

        String sql = "select count(*)  as count from favorite where u_id = ? and a_id = ?; ";

        Map<String, Object> query = null;
        try {
            query = qr.query(sql, new MapHandler(), uId, bId);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return Integer.parseInt(query.get("count").toString());
    }


    public void cancelCollect(int aId, int uId) {
        String sql = "delete from favorite where a_id = ? and u_id = ?";

        try {
            qr.execute(sql, aId, uId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public List findCoCollectPeople(int aId, int uId) {

        String sql = "select real_name as name\n" +
                "from user_info \n" +
                "where user_id \n" +
                "in(select f.u_id from favorite  f where  a_id = ? and u_id in\n" +
                "(select user_focus_id as f_id from user_focus where user_id = ? )) ";

        List<Map<String, Object>> query = null;
        try {
            query = qr.query(sql, new MapListHandler(), aId, uId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    public void updateBrowseCount(Integer aId) {
        String sql = "update article set browse_count = browse_count+1 where id = ? ";
        try {
            qr.execute(sql,aId);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
